package com.csproj.dispatch.service;

import com.csproj.basic.repository.SiteRepository;
import com.csproj.dispatch.util.WarnType;
import com.csproj.dispatch.util.entity.*;
import com.csproj.siiat.base.common.dto.BaseDTO;
import com.csproj.siiat.config.Languagesproperties;
import com.csproj.system.domain.Language;
import com.csproj.system.repository.LanguageRepository;
import com.csproj.system.service.UserInfoService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
@Transactional
public class StatisticsService {

    private final Logger log = LoggerFactory.getLogger(StatisticsService.class);

    private final SiteRepository siteRepository;

    private final EntityManager entityManager;

    private final UserInfoService userInfoService;

    private final Languagesproperties languagesproperties;

    private final LanguageRepository languageRepository;

    public StatisticsService(EntityManager entityManager, SiteRepository siteRepository,
                             UserInfoService userInfoService, Languagesproperties languagesproperties, LanguageRepository languageRepository) {
        this.entityManager = entityManager;
        this.siteRepository = siteRepository;
        this.userInfoService = userInfoService;
        this.languagesproperties = languagesproperties;
        this.languageRepository = languageRepository;
    }

    public BaseDTO usefrequency(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                String launchsiteId, String destinationsiteId) {

        /****************获取统计查询语句*****************/
        String sql = "select dp.route_code routeCode,dp.launchsite_id launchsiteId,dp.destinationsite_id destinationsiteId, count(*) frequency  from t_base_dispatch dp ";
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        StringBuilder sbsql = new StringBuilder(" where 1=1 and dp.route_code is not null ");
        launchsiteId = userInfoService.getCurrentUserSiteId();

        if (!StringUtils.isEmpty(launchsiteId) && !"nositeID".equals(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间

            sbsql.append("and to_char(dp.creation_time,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间

            sbsql.append("and to_char(dp.creation_time,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'");
        }
        sbsql.append(" group by dp.route_code,dp.launchsite_id,dp.destinationsite_id");
        sbsql.append(" order by frequency " + sort);

        sql += sbsql.toString();
        /****************获取统计查询语句*****************/

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteFrequency> routeFrequencies = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            RouteFrequency routeFrequency = new RouteFrequency();
            routeFrequency.setRouteCode(objs[0].toString());
            routeFrequency.setLaunchsite(siteRepository.findById(objs[1].toString()).getSiteName());
            routeFrequency.setDestinationsite(siteRepository.findById(objs[2].toString()).getSiteName());
            routeFrequency.setFrequency(new Integer(objs[3].toString()));
            return routeFrequency;
        }).collect(Collectors.toList());

        if (routeFrequencies.size() > limit) {
            routeFrequencies = routeFrequencies.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(routeFrequencies);
    }

    public List<RouteFrequency> usefrequencyExport(ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                                   String launchsiteId, String destinationsiteId) {
        /****************获取统计查询语句*****************/
        String sql = "select dp.route_code routeCode,dp.launchsite_id launchsiteId,dp.destinationsite_id destinationsiteId, count(*) frequency  from t_base_dispatch dp ";
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        StringBuilder sbsql = new StringBuilder(" where 1=1 and dp.route_code is not null ");
        launchsiteId = userInfoService.getCurrentUserSiteId();

        if (!StringUtils.isEmpty(launchsiteId) && !"nositeID".equals(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间

            sbsql.append("and to_char(dp.creation_time,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间

            sbsql.append("and to_char(dp.creation_time,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'");
        }
        sbsql.append(" group by dp.route_code,dp.launchsite_id,dp.destinationsite_id");
        sbsql.append(" order by frequency " + sort);

        sql += sbsql.toString();
        /****************获取统计查询语句*****************/

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteFrequency> routeFrequencies = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            RouteFrequency routeFrequency = new RouteFrequency();
            routeFrequency.setRouteCode(objs[0].toString());
            routeFrequency.setLaunchsite(siteRepository.findById(objs[1].toString()).getSiteName());
            routeFrequency.setDestinationsite(siteRepository.findById(objs[2].toString()).getSiteName());
            routeFrequency.setFrequency(new Integer(objs[3].toString()));
            return routeFrequency;
        }).collect(Collectors.toList());

        return routeFrequencies;
    }

    public BaseDTO securityTrend(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                 String launchsiteId, String destinationsiteId) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        launchsiteId = userInfoService.getCurrentUserSiteId();

        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (!StringUtils.isEmpty(launchsiteId) && !"nositeID".equals(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "'");
        }
        /****************获取过滤语句*****************/

        String sql = "select bse.routeCode,bse.launchsiteId,bse.destinationsiteId, count(bse.wid) cwid " +
                "from (" +
                "select dp.route_code routeCode,dp.launchsite_id launchsiteId,dp.destinationsite_id destinationsiteId, warn.id wid " +
                " from t_dis_warn_info warn " +
                " left join t_base_dispatch dp " +
                " on warn.dispatch_id=dp.id " +
                "where dp.deletion_time is NULL " + sbsql.toString() + ") bse " +
                "where bse.routeCode is not NULL and bse.wid is not NULL " +
                "group by bse.routeCode,bse.launchsiteId,bse.destinationsiteId " +
                "order by cwid " + sort;


        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteFrequency> routeFrequencies = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            RouteFrequency routeFrequency = new RouteFrequency();
            routeFrequency.setRouteCode(objs[0].toString());
            routeFrequency.setLaunchsite(siteRepository.findById(objs[1].toString()).getSiteName());
            routeFrequency.setDestinationsite(siteRepository.findById(objs[2].toString()).getSiteName());
            routeFrequency.setFrequency(new Integer(objs[3].toString()));
            return routeFrequency;
        }).collect(Collectors.toList());

        if (routeFrequencies.size() > limit) {
            routeFrequencies = routeFrequencies.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(routeFrequencies);

    }

    public List<RouteFrequency> securityTrendExport(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                                    String launchsiteId, String destinationsiteId) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        launchsiteId = userInfoService.getCurrentUserSiteId();

        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (!StringUtils.isEmpty(launchsiteId) && !"nositeID".equals(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "'");
        }
        /****************获取过滤语句*****************/

        String sql = "select bse.routeCode,bse.launchsiteId,bse.destinationsiteId, count(bse.wid) cwid " +
                "from (" +
                "select dp.route_code routeCode,dp.launchsite_id launchsiteId,dp.destinationsite_id destinationsiteId, warn.id wid " +
                " from t_dis_warn_info warn " +
                " left join t_base_dispatch dp " +
                " on warn.dispatch_id=dp.id " +
                "where dp.deletion_time is NULL " + sbsql.toString() + ") bse " +
                "where bse.routeCode is not NULL and bse.wid is not NULL " +
                "group by bse.routeCode,bse.launchsiteId,bse.destinationsiteId " +
                "order by cwid " + sort;


        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteFrequency> routeFrequencies = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            RouteFrequency routeFrequency = new RouteFrequency();
            routeFrequency.setRouteCode(objs[0].toString());
            routeFrequency.setLaunchsite(siteRepository.findById(objs[1].toString()).getSiteName());
            routeFrequency.setDestinationsite(siteRepository.findById(objs[2].toString()).getSiteName());
            routeFrequency.setFrequency(new Integer(objs[3].toString()));
            return routeFrequency;
        }).collect(Collectors.toList());

        if (routeFrequencies.size() > limit) {
            routeFrequencies = routeFrequencies.subList(0, limit);
        }

        return routeFrequencies;
    }


    public BaseDTO warnTrend(ZonedDateTime startTime, ZonedDateTime endTime, String launchsiteId,
                             String destinationsiteId, String routeId) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (!StringUtils.isEmpty(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') >='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "'");
        }
        if (!StringUtils.isEmpty(routeId)) {//路径
            sbsql.append("and dp.route_id = '" + routeId + "' ");
        }
        /****************获取过滤语句*****************/

        String sql = "select warn.warn_type wtype, count(warn.warn_type) wc" +
                " from t_dis_warn_info warn " +
                " left join t_base_dispatch dp " +
                " on warn.dispatch_id=dp.id " +
                "where dp.deletion_time is NULL and warn.deletion_time is NULL " + sbsql.toString() +
                " group by warn.warn_type";
        Map<String, Object> currentMp = new HashMap<String, Object>() {
        };
        List<Language> l = languageRepository.findByDefault();
        Language first = null;
        if (l.size() > 0) {
            first = l.get(0);
        }

        if (first != null) {
            if (first.getName().equals("zh")) {
                currentMp = languagesproperties.getZh();
            } else if (first.getName().equals("en")) {
                currentMp = languagesproperties.getEn();

            } else if (first.getName().equals("pu")) {
                currentMp = languagesproperties.getPu();
            }
        }
        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteTrend> resultList = new ArrayList<>();
        for (Object o : objectList) {
            Object[] objs = (Object[]) o;
            RouteTrend routeTrend = new RouteTrend();
            if (objs[0] != null) {
                String warnType = currentMp.get(WarnType.getName(new Integer(objs[0].toString()))).toString();
                routeTrend.setWarnType(warnType);
            }
            routeTrend.setFrequency(new Integer(objs[1].toString()));
            resultList.add(routeTrend);
        }

        return BaseDTO.createBaseDTO(resultList);
    }

    public List<RouteTrend> warnTrendExport(ZonedDateTime startTime, ZonedDateTime endTime, String launchsiteId,
                                            String destinationsiteId, String routeId) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (!StringUtils.isEmpty(launchsiteId)) {//起始站点
            sbsql.append("and dp.launchsite_id = '" + launchsiteId + "' ");
        }
        if (!StringUtils.isEmpty(destinationsiteId)) {//目的站点
            sbsql.append("and dp.destinationsite_id = '" + destinationsiteId + "' ");
        }
        if (startTime != null) {//开始时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') >='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {//结束时间
            sbsql.append("and to_char(warn.warn_time,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "'");
        }
        if (!StringUtils.isEmpty(routeId)) {//路径
            sbsql.append("and dp.route_id = '" + routeId + "' ");
        }
        /****************获取过滤语句*****************/

        String sql = "select warn.warn_type wtype, count(warn.warn_type) wc" +
                " from t_dis_warn_info warn " +
                " left join t_base_dispatch dp " +
                " on warn.dispatch_id=dp.id " +
                "where dp.deletion_time is NULL and warn.deletion_time is NULL " + sbsql.toString() +
                " group by warn.warn_type";
        Map<String, Object> currentMp = new HashMap<String, Object>() {
        };
        List<Language> l = languageRepository.findByDefault();
        Language first = null;
        if (l.size() > 0) {
            first = l.get(0);
        }

        if (first != null) {
            if (first.getName().equals("zh")) {
                currentMp = languagesproperties.getZh();
            } else if (first.getName().equals("en")) {
                currentMp = languagesproperties.getEn();

            } else if (first.getName().equals("pu")) {
                currentMp = languagesproperties.getPu();
            }
        }
        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<RouteTrend> resultList = new ArrayList<>();
        for (Object o : objectList) {
            Object[] objs = (Object[]) o;
            RouteTrend routeTrend = new RouteTrend();
            if (objs[0] != null) {
                String warnType = currentMp.get(WarnType.getName(new Integer(objs[0].toString()))).toString();
                routeTrend.setWarnType(warnType);
            }
            routeTrend.setFrequency(new Integer(objs[1].toString()));
            resultList.add(routeTrend);
        }

        return resultList;
    }

    /**
     * 路径使用时间统计
     *
     * @return
     */
    public BaseDTO arriveTimeCount(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        StringBuilder dbsql = new StringBuilder();
        if (startTime != null) {
            dbsql.append(" and to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {
            dbsql.append(" and to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'");
        }
        /****************获取过滤语句*****************/

//        String sql = "SELECT" +
//                "	dp.route_code routeCode," +
//                "	(" +
//                "		SELECT" +
//                "			site1.site_name" +
//                "		FROM" +
//                "			t_base_site site1" +
//                "		WHERE" +
//                "			site1.id = dp.launchsite_id" +
//                "	) launchsiteId," +
//                "	(" +
//                "		SELECT" +
//                "			site2.site_name" +
//                "		FROM" +
//                "			t_base_site site2" +
//                "		WHERE" +
//                "			site2.id = dp.destinationsite_id" +
//                "	) destinationsiteId," +
//                "	sum(" +
//                "		round(" +
//                "			(" +
//                "				(dp.arrive_time - dp.launch_time)*24*60*60" +
//                "			)" +
//                "		)" +
//                "	) timecount " +
//                "FROM" +
//                "	t_base_dispatch dp " +
//                "WHERE" +
//                "	dp.deletion_time IS NULL " +
//                "AND dp.arrive_time IS NOT NULL " +
//                "AND dp.launch_time IS NOT NULL " +
//                dbsql.toString()+
//                " GROUP BY" +
//                "	dp.route_code," +
//                "	dp.launchsite_id," +
//                "	dp.destinationsite_id " +
//                "ORDER BY" +
//                "	timecount "+sort;

        String sql = "SELECT dp.route_code routeCode," +
                "   (SELECT site1.site_name " +
                "   FROM   " +
                "   t_base_site site1  " +
                "   WHERE   site1.id = dp.launchsite_id ) launchsiteId," +
                "   ( SELECT   site2.site_name  " +
                "   FROM   t_base_site site2  " +
                "   WHERE   site2.id = dp.destinationsite_id ) destinationsiteId, " +
                "   sum(" +
                "       round(" +
                "           TO_NUMBER(" +
                "               TO_DATE (TO_CHAR (dp.arrive_time,'yyyy-mm-dd hh24:mi:ss' ),'yyyy-mm-dd hh24:mi:ss') - " +
                "               TO_DATE (TO_CHAR (dp.launch_time ,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')" +
                "               )" +
                "           *24*60*60 )" +
                "   ) timecount" +
                "   FROM t_base_dispatch dp " +
                "   WHERE " +
                "   dp.deletion_time IS NULL " +
                "   AND dp.arrive_time IS NOT NULL " +
                "   AND dp.launch_time IS NOT NULL  " +
                dbsql.toString() +
                "   GROUP BY dp.route_code, dp.launchsite_id, dp.destinationsite_id " +
                "   ORDER BY timecount desc";


        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();

        // 将查询结果转为对象列表，返回给前台
        List<ArriveTimeCount> arriveTimeCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            ArriveTimeCount arriveTimeCount = new ArriveTimeCount();
            arriveTimeCount.setRouteCode(objs[0].toString());
            arriveTimeCount.setLaunchsite(objs[1].toString());
            arriveTimeCount.setDestinationsite(objs[2].toString());
            arriveTimeCount.setTimes(Double.parseDouble(objs[3].toString()));
            return arriveTimeCount;
        }).collect(Collectors.toList());

        if (arriveTimeCounts.size() > limit) {
            arriveTimeCounts = arriveTimeCounts.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(arriveTimeCounts);
    }

    public List<ArriveTimeCount> arriveTimeCountForExport(ZonedDateTime startTime, ZonedDateTime endTime, String sort) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        StringBuilder dbsql = new StringBuilder();
        if (startTime != null) {
            dbsql.append(" and to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss') >='" + startTime.format(formatter) + "'");
        }
        if (endTime != null) {
            dbsql.append(" and to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss') <='" + endTime.format(formatter) + "'");
        }
        /****************获取过滤语句*****************/

        String sql = "SELECT dp.route_code routeCode," +
                "   (SELECT site1.site_name " +
                "   FROM   " +
                "   t_base_site site1  " +
                "   WHERE   site1.id = dp.launchsite_id ) launchsiteId," +
                "   ( SELECT   site2.site_name  " +
                "   FROM   t_base_site site2  " +
                "   WHERE   site2.id = dp.destinationsite_id ) destinationsiteId, " +
                "   sum(" +
                "       round(" +
                "           TO_NUMBER(" +
                "               TO_DATE (TO_CHAR (dp.arrive_time,'yyyy-mm-dd hh24:mi:ss' ),'yyyy-mm-dd hh24:mi:ss') - " +
                "               TO_DATE (TO_CHAR (dp.launch_time ,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')" +
                "               )" +
                "           *24*60*60 )" +
                "   ) timecount" +
                "   FROM t_base_dispatch dp " +
                "   WHERE " +
                "   dp.deletion_time IS NULL " +
                "   AND dp.arrive_time IS NOT NULL " +
                "   AND dp.launch_time IS NOT NULL  " +
                dbsql.toString() +
                "   GROUP BY dp.route_code, dp.launchsite_id, dp.destinationsite_id " +
                "   ORDER BY timecount desc";
//        String sql = "SELECT dp.route_code routeCode" +
//                "   (SELECT site1.site_name " +
//                "   FROM   " +
//                "   t_base_site site1  " +
//                "   WHERE   site1.id = dp.launchsite_id ) launchsiteId," +
//                "   ( SELECT   site2.site_name  " +
//                "   FROM   t_base_site site2  " +
//                "   WHERE   site2.id = dp.destinationsite_id ) destinationsiteId, " +
//                "   sum(" +
//                "   round(TO_NUMBER(dp.arrive_time - dp.launch_time)*24*60*60 ) ) timecount" +
//                "   FROM t_base_dispatch dp " +
//                "   WHERE " +
//                "   dp.deletion_time IS NULL " +
//                "   AND dp.arrive_time IS NOT NULL " +
//                "   AND dp.launch_time IS NOT NULL  " +
//                dbsql.toString() +
//                "   GROUP BY dp.route_code, dp.launchsite_id, dp.destinationsite_id " +
//                "   ORDER BY timecount desc";


        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();

        // 将查询结果转为对象列表，返回给前台
        List<ArriveTimeCount> arriveTimeCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            ArriveTimeCount arriveTimeCount = new ArriveTimeCount();
            arriveTimeCount.setRouteCode(objs[0].toString());
            arriveTimeCount.setLaunchsite(objs[1].toString());
            arriveTimeCount.setDestinationsite(objs[2].toString());
            int totalCount = Integer.parseInt(objs[3].toString());
            String result ="";
            if(totalCount>0)
            {
                int day=totalCount/(60*60*24);//换成天
                int hour=(totalCount-(60*60*24*day))/3600;//总秒数-换算成天的秒数=剩余的秒数    剩余的秒数换算为小时
                int minute=(totalCount-60*60*24*day-3600*hour)/60;//总秒数-换算成天的秒数-换算成小时的秒数=剩余的秒数    剩余的秒数换算为分
                int second=totalCount-60*60*24*day-3600*hour-60*minute;//总秒数-换算成天的秒数-换算成小时的秒数-换算为分的秒数=剩余的秒数   
                Map<String, Object> currentMp = new HashMap<String, Object>() {
                };
                List<Language> l = languageRepository.findByDefault();
                Language first = null;
                if (l.size() > 0) {
                    first = l.get(0);
                }

                if (first != null) {
                    if (first.getName().equals("zh")) {
                        currentMp = languagesproperties.getZh();
                    } else if (first.getName().equals("en")) {
                        currentMp = languagesproperties.getEn();

                    } else if (first.getName().equals("pu")) {
                        currentMp = languagesproperties.getPu();
                    }
                }

                result = "" + second + currentMp.get("minutes") ;

                if (minute > 0) {
                    result = "" + minute + currentMp.get("second") + result;
                }
                if (hour > 0) {
                    result = "" + hour + currentMp.get("hours") + result;
                }
                if (day > 0) {
                    result = "" + day + currentMp.get("day") + result;
                }
            }
            arriveTimeCount.setTimeStr(result);
            return arriveTimeCount;
        }).collect(Collectors.toList());


        return arriveTimeCounts;
    }

    /**
     * 关锁使用统计
     *
     * @return
     */
    public BaseDTO lockUseCont(Integer limit, String siteId, ZonedDateTime startTime, ZonedDateTime endTime, String sort) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String currentUserSiteId = userInfoService.getCurrentUserSiteId();
        String currentSiteSql = "";
        if (StringUtils.isNotBlank(currentUserSiteId) && !currentUserSiteId.equals("nositeID")) {
            currentSiteSql = " where site.id ='" + currentUserSiteId + "' ";
        } else if (StringUtils.isNotBlank(siteId)) {
            currentSiteSql = " where site.id ='" + siteId + "' ";
        }
        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (startTime != null) {//开始时间
            sbsql.append("and (to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "' or to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "')");
        }
        if (endTime != null) {//结束时间
            sbsql.append("and (to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "' or to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "')");
        }

        /****************获取过滤语句*****************/

        String sql = "select lcount.lockCode lockCode,site.site_name siteName,lcount.frequency frequency from " +
                "(select dp.lock_code lockCode, count(*) frequency  " +
                "from t_base_dispatch dp  " +
                "where 1=1 and dp.deletion_time is NULL " +
                "and dp.lock_code is not null  " + sbsql.toString() +
                " group by dp.lock_code order by frequency desc) lcount left join t_base_electronic_lock elock " +
                "on elock.lock_no=lcount.lockCode " +
                "left join t_base_site site on site.id=elock.site_id" +
                currentSiteSql+
                " order by frequency " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockUseCount> lockUseCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockUseCount lockUseCount = new LockUseCount();
            if (objs[0] != null) {
                lockUseCount.setLockCode(objs[0].toString());
            }
            if (objs[1] != null) {
                lockUseCount.setSiteName(objs[1].toString());
            }
            if (objs[2] != null) {
                lockUseCount.setFrequency(new Integer(objs[2].toString()));
            }

            return lockUseCount;
        }).collect(Collectors.toList());

        if (lockUseCounts.size() > limit) {
            lockUseCounts = lockUseCounts.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(lockUseCounts);
    }

    public List<LockUseCount> lockUseContExport(Integer limit, String siteId, ZonedDateTime startTime, ZonedDateTime endTime, String sort) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String currentUserSiteId = userInfoService.getCurrentUserSiteId();
        String currentSiteSql = "";
        if (StringUtils.isNotBlank(currentUserSiteId) && !currentUserSiteId.equals("nositeID")) {
            currentSiteSql = " where site.id ='" + currentUserSiteId + "' ";
        } else if (StringUtils.isNotBlank(siteId)) {
            currentSiteSql = " where site.id ='" + siteId + "' ";
        }
        /****************获取过滤语句*****************/
        StringBuilder sbsql = new StringBuilder();
        if (startTime != null) {//开始时间
            sbsql.append(" and (to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "' or to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss') >= '" + startTime.format(formatter) + "')");
        }
        if (endTime != null) {//结束时间
            sbsql.append(" and (to_char(dp.launch_time ,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "' or to_char(dp.arrive_time ,'yyyy-MM-dd HH24:mi:ss') <= '" + endTime.format(formatter) + "')");
        }

        /****************获取过滤语句*****************/

        String sql = "select lcount.lockCode lockCode,site.site_name siteName,lcount.frequency frequency from " +
                "(select dp.lock_code lockCode, count(*) frequency  " +
                "from t_base_dispatch dp  " +
                "where 1=1 and dp.deletion_time is NULL " +
                "and dp.lock_code is not null  " + sbsql.toString() +
                " group by dp.lock_code order by frequency desc) lcount left join t_base_electronic_lock elock " +
                "on elock.lock_no=lcount.lockCode " +
                "left join t_base_site site on site.id=elock.site_id" +
                currentSiteSql+
                " order by frequency " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockUseCount> lockUseCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockUseCount lockUseCount = new LockUseCount();
            if (objs[0] != null) {
                lockUseCount.setLockCode(objs[0].toString());
            }
            if (objs[1] != null) {
                lockUseCount.setSiteName(objs[1].toString());
            }
            if (objs[2] != null) {
                lockUseCount.setFrequency(new Integer(objs[2].toString()));
            }

            return lockUseCount;
        }).collect(Collectors.toList());

        if (lockUseCounts.size() > limit) {
            lockUseCounts = lockUseCounts.subList(0, limit);
        }

        return lockUseCounts;
    }

    /**
     * 关锁业务量统计
     *
     * @return
     */
    public BaseDTO lockBusinessCount(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                     String siteId, Integer busType) {

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        String unionsql = null;
        if (busType == 1) {//转运业务量
            unionsql = "		SELECT" +
                    "			dp1.launchsite_id la," +
                    "			count(*) c " +
                    "		FROM" +
                    "			t_base_dispatch dp1 " +
                    "		WHERE" +
                    "			dp1.deletion_time IS NULL " +
                    "		AND dp1.launch_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp1.launchsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "		GROUP BY " +
                    "			dp1.launchsite_id ";

        } else if (busType == 2) {//抵运业务量
            unionsql = "			SELECT " +
                    "				dp2.destinationsite_id la, " +
                    "				count(*) c " +
                    "			FROM " +
                    "				t_base_dispatch dp2 " +
                    "			WHERE " +
                    "				dp2.deletion_time IS NULL " +
                    "			AND dp2.launch_time IS NOT NULL " +
                    "			AND dp2.arrive_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp2.destinationsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "			GROUP BY " +
                    "				dp2.destinationsite_id ";
        } else {//全部业务
            unionsql = "		SELECT" +
                    "			dp1.launchsite_id la," +
                    "			count(*) c " +
                    "		FROM" +
                    "			t_base_dispatch dp1 " +
                    "		WHERE" +
                    "			dp1.deletion_time IS NULL " +
                    "		AND dp1.launch_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp1.launchsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "		GROUP BY " +
                    "			dp1.launchsite_id " +
                    "		UNION ALL " +
                    "			SELECT " +
                    "				dp2.destinationsite_id la, " +
                    "				count(*) c " +
                    "			FROM " +
                    "				t_base_dispatch dp2 " +
                    "			WHERE " +
                    "				dp2.deletion_time IS NULL " +
                    "			AND dp2.launch_time IS NOT NULL " +
                    "			AND dp2.arrive_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp2.destinationsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "			GROUP BY " +
                    "				dp2.destinationsite_id ";
        }

        /****************获取过滤语句*****************/

        String sql = "SELECT" +
                "	(select site.site_name from t_base_site site where site.id = cc.la)," +
                "	SUM(cc.c) ss " +
                "FROM" +
                "	(" +
                unionsql +
                "	) cc " +
                "GROUP BY " +
                "	cc.la order by ss " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockBusinessCount> lockBusinessCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockBusinessCount lockBusinessCount = new LockBusinessCount();
            lockBusinessCount.setSiteName(objs[0].toString());
            lockBusinessCount.setBus(Integer.parseInt(objs[1].toString()));

            return lockBusinessCount;
        }).collect(Collectors.toList());

        if (lockBusinessCounts.size() > limit) {
            lockBusinessCounts = lockBusinessCounts.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(lockBusinessCounts);

    }

    public List<LockBusinessCount> lockBusinessCountExport(Integer limit, ZonedDateTime startTime, ZonedDateTime endTime, String sort,
                                                           String siteId, Integer busType){
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        /****************获取过滤语句*****************/
        String unionsql = null;
        if (busType == 1) {//转运业务量
            unionsql = "		SELECT" +
                    "			dp1.launchsite_id la," +
                    "			count(*) c " +
                    "		FROM" +
                    "			t_base_dispatch dp1 " +
                    "		WHERE" +
                    "			dp1.deletion_time IS NULL " +
                    "		AND dp1.launch_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp1.launchsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "		GROUP BY " +
                    "			dp1.launchsite_id ";

        } else if (busType == 2) {//抵运业务量
            unionsql = "			SELECT " +
                    "				dp2.destinationsite_id la, " +
                    "				count(*) c " +
                    "			FROM " +
                    "				t_base_dispatch dp2 " +
                    "			WHERE " +
                    "				dp2.deletion_time IS NULL " +
                    "			AND dp2.launch_time IS NOT NULL " +
                    "			AND dp2.arrive_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp2.destinationsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "			GROUP BY " +
                    "				dp2.destinationsite_id ";
        } else {//全部业务
            unionsql = "		SELECT" +
                    "			dp1.launchsite_id la," +
                    "			count(*) c " +
                    "		FROM" +
                    "			t_base_dispatch dp1 " +
                    "		WHERE" +
                    "			dp1.deletion_time IS NULL " +
                    "		AND dp1.launch_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp1.launchsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp1.launch_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "		GROUP BY " +
                    "			dp1.launchsite_id " +
                    "		UNION ALL " +
                    "			SELECT " +
                    "				dp2.destinationsite_id la, " +
                    "				count(*) c " +
                    "			FROM " +
                    "				t_base_dispatch dp2 " +
                    "			WHERE " +
                    "				dp2.deletion_time IS NULL " +
                    "			AND dp2.launch_time IS NOT NULL " +
                    "			AND dp2.arrive_time IS NOT NULL " +
                    (!StringUtils.isEmpty(siteId) ? " and dp2.destinationsite_id='" + siteId + "'" : " ") +
                    (startTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')>='" + startTime.format(formatter) + "'" : " ") +
                    (endTime != null ? " and to_char(dp2.arrive_time ,'yyyy-MM-dd HH24:mi:ss')<='" + endTime.format(formatter) + "'" : " ") +
                    "			GROUP BY " +
                    "				dp2.destinationsite_id ";
        }

        /****************获取过滤语句*****************/

        String sql = "SELECT" +
                "	(select site.site_name from t_base_site site where site.id = cc.la)," +
                "	SUM(cc.c) ss " +
                "FROM" +
                "	(" +
                unionsql +
                "	) cc " +
                "GROUP BY " +
                "	cc.la order by ss " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockBusinessCount> lockBusinessCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockBusinessCount lockBusinessCount = new LockBusinessCount();
            lockBusinessCount.setSiteName(objs[0].toString());
            lockBusinessCount.setBus(Integer.parseInt(objs[1].toString()));

            return lockBusinessCount;
        }).collect(Collectors.toList());

        if (lockBusinessCounts.size() > limit) {
            lockBusinessCounts = lockBusinessCounts.subList(0, limit);
        }

        return lockBusinessCounts;
    }
    public BaseDTO lockDistributionCount(Integer limit, String sort, String siteId) {


//        String sql = "SELECT" +
//                "	(select site.site_name from t_base_site site where site.id=elock.now_site_id) sname," +
//                "	count(*) zt" +
//                " FROM" +
//                "	t_base_electronic_lock elock " +
//                "WHERE" +
//                "	elock.enable_flag = TRUE " +
//                "AND elock.deletion_time IS NULL " +
//                (StringUtils.isEmpty(siteId)?"":(" and elock.now_site_id='"+siteId+"' "))+
//                "GROUP BY" +
//                "	sname order by zt "+sort;

        String sql = "select site.site_name sname, counte.zt from " +
                "   (SELECT elock.now_site_id, count(*) zt FROM" +
                "   t_base_electronic_lock elock WHERE elock.enable_flag = 1 " +
                "   AND elock.deletion_time IS NULL " +
                (StringUtils.isEmpty(siteId) ? "" : (" and elock.now_site_id='" + siteId + "' ")) +
                "   GROUP BY elock.now_site_id) " +
                "   counte left join  t_base_site site " +
                "   on counte.now_site_id = site.id" +
                "   order by counte.zt " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockBusinessCount> lockBusinessCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockBusinessCount lockBusinessCount = new LockBusinessCount();

            lockBusinessCount.setSiteName((objs[0]==null?"":objs[0]).toString());
            lockBusinessCount.setBus(Integer.parseInt(objs[1].toString()));
            return lockBusinessCount;
        }).collect(Collectors.toList());

        if (lockBusinessCounts.size() > limit) {
            lockBusinessCounts = lockBusinessCounts.subList(0, limit);
        }

        return BaseDTO.createBaseDTO(lockBusinessCounts);

    }

    public List<LockBusinessCount> lockDistributionCountExport(Integer limit, String sort, String siteId) {


//        String sql = "SELECT" +
//                "	(select site.site_name from t_base_site site where site.id=elock.now_site_id) sname," +
//                "	count(*) zt" +
//                " FROM" +
//                "	t_base_electronic_lock elock " +
//                "WHERE" +
//                "	elock.enable_flag = TRUE " +
//                "AND elock.deletion_time IS NULL " +
//                (StringUtils.isEmpty(siteId)?"":(" and elock.now_site_id='"+siteId+"' "))+
//                "GROUP BY" +
//                "	sname order by zt "+sort;

        String sql = "select site.site_name sname, counte.zt from " +
                "   (SELECT elock.now_site_id, count(*) zt FROM" +
                "   t_base_electronic_lock elock WHERE elock.enable_flag = 1 " +
                "   AND elock.deletion_time IS NULL " +
                (StringUtils.isEmpty(siteId) ? "" : (" and elock.now_site_id='" + siteId + "' ")) +
                "   GROUP BY elock.now_site_id) " +
                "   counte left join  t_base_site site " +
                "   on counte.now_site_id = site.id" +
                "   order by counte.zt " + sort;

        Query query = entityManager.createNativeQuery(sql);
        List<Object> objectList = query.getResultList();
        List<LockBusinessCount> lockBusinessCounts = objectList.stream().map(n -> {
            Object[] objs = (Object[]) n;
            LockBusinessCount lockBusinessCount = new LockBusinessCount();
            lockBusinessCount.setSiteName(objs[0].toString());
            lockBusinessCount.setBus(Integer.parseInt(objs[1].toString()));
            return lockBusinessCount;
        }).collect(Collectors.toList());

//        if (lockBusinessCounts.size() > limit) {
//            lockBusinessCounts = lockBusinessCounts.subList(0, limit);
//        }

        return lockBusinessCounts;

    }


}
